"""
期权数据访问层 (DAO)
提供期权相关数据的专用访问接口
"""

from typing import List, Optional, Dict, Any
from datetime import datetime, date, timedelta
from decimal import Decimal
from sqlalchemy import select, and_, or_, desc, asc, func
from sqlalchemy.ext.asyncio import AsyncSession

from .base import FinancialDataDAO
from ..models.option import (
    OptionBasicInfo,
    OptionDailyData,
    OptionMinuteData,
    OptionChain,
    OptionExerciseData
)


class OptionBasicInfoDAO(FinancialDataDAO[OptionBasicInfo]):
    """期权基本信息数据访问层"""
    
    model = OptionBasicInfo
    
    async def get_by_option_code(self, session: AsyncSession, option_code: str) -> Optional[OptionBasicInfo]:
        """根据期权代码获取期权基本信息"""
        stmt = select(self.model).where(self.model.option_code == option_code)
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_by_underlying_code(self, session: AsyncSession, underlying_code: str) -> List[OptionBasicInfo]:
        """根据标的资产代码获取期权列表"""
        stmt = select(self.model).where(self.model.underlying_code == underlying_code)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_exchange(self, session: AsyncSession, exchange: str) -> List[OptionBasicInfo]:
        """根据交易所获取期权列表"""
        stmt = select(self.model).where(self.model.exchange == exchange)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_active_options(self, session: AsyncSession) -> List[OptionBasicInfo]:
        """获取活跃期权合约列表"""
        current_date = datetime.now().date()
        stmt = select(self.model).where(
            and_(
                self.model.contract_status == '交易',
                or_(
                    self.model.expire_date.is_(None),
                    self.model.expire_date > current_date
                )
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_expiring_options(
        self,
        session: AsyncSession,
        days_ahead: int = 30
    ) -> List[OptionBasicInfo]:
        """获取即将到期的期权合约"""
        current_date = datetime.now().date()
        expiry_date = current_date + timedelta(days=days_ahead)
        
        stmt = select(self.model).where(
            and_(
                self.model.contract_status == '交易',
                self.model.expire_date <= expiry_date,
                self.model.expire_date > current_date
            )
        ).order_by(asc(self.model.expire_date))
        
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_option_type(
        self,
        session: AsyncSession,
        option_type: str,
        underlying_code: Optional[str] = None
    ) -> List[OptionBasicInfo]:
        """根据期权类型获取合约列表"""
        conditions = [self.model.option_type == option_type]
        
        if underlying_code:
            conditions.append(self.model.underlying_code == underlying_code)
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_strike_range(
        self,
        session: AsyncSession,
        underlying_code: str,
        min_strike: Decimal,
        max_strike: Decimal,
        expire_date: Optional[date] = None
    ) -> List[OptionBasicInfo]:
        """根据行权价范围获取期权合约"""
        conditions = [
            self.model.underlying_code == underlying_code,
            self.model.strike_price >= min_strike,
            self.model.strike_price <= max_strike
        ]
        
        if expire_date:
            conditions.append(self.model.expire_date == expire_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(asc(self.model.strike_price))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_option_chain_basic(
        self,
        session: AsyncSession,
        underlying_code: str,
        expire_date: date
    ) -> Dict[str, List[OptionBasicInfo]]:
        """获取期权链基础数据"""
        stmt = select(self.model).where(
            and_(
                self.model.underlying_code == underlying_code,
                self.model.expire_date == expire_date
            )
        ).order_by(asc(self.model.strike_price))
        
        result = await session.execute(stmt)
        contracts = list(result.scalars().all())
        
        # 按期权类型分组
        call_options = [c for c in contracts if c.option_type == 'Call']
        put_options = [c for c in contracts if c.option_type == 'Put']
        
        return {
            'call_options': call_options,
            'put_options': put_options
        }
    
    async def get_main_contracts(self, session: AsyncSession) -> List[OptionBasicInfo]:
        """获取主力期权合约"""
        stmt = select(self.model).where(self.model.is_main == True)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def search_options(
        self,
        session: AsyncSession,
        keyword: str,
        exchange: Optional[str] = None,
        option_type: Optional[str] = None
    ) -> List[OptionBasicInfo]:
        """搜索期权合约"""
        conditions = [
            or_(
                self.model.option_name.ilike(f'%{keyword}%'),
                self.model.option_code.ilike(f'%{keyword}%'),
                self.model.underlying_name.ilike(f'%{keyword}%')
            )
        ]
        
        if exchange:
            conditions.append(self.model.exchange == exchange)
        
        if option_type:
            conditions.append(self.model.option_type == option_type)
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())


class OptionDailyDataDAO(FinancialDataDAO[OptionDailyData]):
    """期权日行情数据访问层"""
    
    model = OptionDailyData
    
    async def get_by_option_and_date(
        self,
        session: AsyncSession,
        option_code: str,
        trade_date: date
    ) -> Optional[OptionDailyData]:
        """获取指定期权指定日期的行情数据"""
        stmt = select(self.model).where(
            and_(
                self.model.option_code == option_code,
                self.model.trade_date == trade_date
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_price_history(
        self,
        session: AsyncSession,
        option_code: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
        limit: int = 1000
    ) -> List[OptionDailyData]:
        """获取期权价格历史数据"""
        conditions = [self.model.option_code == option_code]
        
        if start_date:
            conditions.append(self.model.trade_date >= start_date)
        if end_date:
            conditions.append(self.model.trade_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.trade_date))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_data(
        self,
        session: AsyncSession,
        option_code: str,
        days: int = 30
    ) -> List[OptionDailyData]:
        """获取最近N天的期权行情数据"""
        from_date = datetime.now().date() - timedelta(days=days)
        return await self.get_price_history(session, option_code, from_date)
    
    async def get_latest_data(self, session: AsyncSession, option_code: str) -> Optional[OptionDailyData]:
        """获取期权最新行情数据"""
        stmt = (
            select(self.model)
            .where(self.model.option_code == option_code)
            .order_by(desc(self.model.trade_date))
            .limit(1)
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_high_volume_options(
        self,
        session: AsyncSession,
        trade_date: date,
        min_volume: int = 1000,
        limit: int = 50
    ) -> List[OptionDailyData]:
        """获取高成交量期权"""
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.trade_date == trade_date,
                    self.model.volume >= min_volume
                )
            )
            .order_by(desc(self.model.volume))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_high_iv_options(
        self,
        session: AsyncSession,
        trade_date: date,
        min_iv: Decimal = Decimal('0.5'),  # 50%
        limit: int = 50
    ) -> List[OptionDailyData]:
        """获取高隐含波动率期权"""
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.trade_date == trade_date,
                    self.model.implied_volatility >= min_iv
                )
            )
            .order_by(desc(self.model.implied_volatility))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def calculate_option_metrics(
        self,
        session: AsyncSession,
        option_code: str,
        days: int = 20
    ) -> Dict[str, float]:
        """计算期权指标"""
        from_date = datetime.now().date() - timedelta(days=days)
        price_data = await self.get_price_history(session, option_code, from_date)
        
        if not price_data:
            return {
                'avg_iv': 0.0,
                'avg_volume': 0.0,
                'price_volatility': 0.0,
                'avg_open_interest': 0.0,
                'avg_delta': 0.0,
                'avg_gamma': 0.0,
                'avg_theta': 0.0,
                'avg_vega': 0.0
            }
        
        # 计算平均隐含波动率
        iv_values = [float(data.implied_volatility) for data in price_data if data.implied_volatility]
        avg_iv = sum(iv_values) / len(iv_values) if iv_values else 0
        
        # 计算平均成交量
        volumes = [data.volume for data in price_data if data.volume]
        avg_volume = sum(volumes) / len(volumes) if volumes else 0
        
        # 计算价格波动率
        prices = [float(data.close_price) for data in reversed(price_data) if data.close_price]
        if len(prices) > 1:
            returns = [(prices[i] - prices[i-1]) / prices[i-1] for i in range(1, len(prices))]
            import statistics
            price_volatility = statistics.stdev(returns) if len(returns) > 1 else 0
        else:
            price_volatility = 0
        
        # 计算平均持仓量
        open_interests = [data.open_interest for data in price_data if data.open_interest]
        avg_open_interest = sum(open_interests) / len(open_interests) if open_interests else 0
        
        # 计算平均希腊字母
        delta_values = [float(data.delta) for data in price_data if data.delta]
        avg_delta = sum(delta_values) / len(delta_values) if delta_values else 0
        
        gamma_values = [float(data.gamma) for data in price_data if data.gamma]
        avg_gamma = sum(gamma_values) / len(gamma_values) if gamma_values else 0
        
        theta_values = [float(data.theta) for data in price_data if data.theta]
        avg_theta = sum(theta_values) / len(theta_values) if theta_values else 0
        
        vega_values = [float(data.vega) for data in price_data if data.vega]
        avg_vega = sum(vega_values) / len(vega_values) if vega_values else 0
        
        return {
            'avg_iv': avg_iv,
            'avg_volume': avg_volume,
            'price_volatility': price_volatility,
            'avg_open_interest': avg_open_interest,
            'avg_delta': avg_delta,
            'avg_gamma': avg_gamma,
            'avg_theta': avg_theta,
            'avg_vega': avg_vega
        }
    
    async def get_options_by_underlying(
        self,
        session: AsyncSession,
        underlying_close: Decimal,
        trade_date: date,
        tolerance: Decimal = Decimal('0.05')  # 5%容差
    ) -> List[OptionDailyData]:
        """根据标的价格获取相关期权数据"""
        min_price = underlying_close * (1 - tolerance)
        max_price = underlying_close * (1 + tolerance)
        
        stmt = select(self.model).where(
            and_(
                self.model.trade_date == trade_date,
                self.model.underlying_close >= min_price,
                self.model.underlying_close <= max_price
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())


class OptionMinuteDataDAO(FinancialDataDAO[OptionMinuteData]):
    """期权分钟数据访问层"""
    
    model = OptionMinuteData
    
    async def get_by_option_and_time(
        self,
        session: AsyncSession,
        option_code: str,
        trade_time: datetime
    ) -> Optional[OptionMinuteData]:
        """获取指定期权指定时间的分钟数据"""
        stmt = select(self.model).where(
            and_(
                self.model.option_code == option_code,
                self.model.trade_time == trade_time
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_intraday_data(
        self,
        session: AsyncSession,
        option_code: str,
        trade_date: date
    ) -> List[OptionMinuteData]:
        """获取期权日内分钟数据"""
        start_time = datetime.combine(trade_date, datetime.min.time())
        end_time = datetime.combine(trade_date, datetime.max.time())
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.option_code == option_code,
                    self.model.trade_time >= start_time,
                    self.model.trade_time <= end_time
                )
            )
            .order_by(asc(self.model.trade_time))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_minutes(
        self,
        session: AsyncSession,
        option_code: str,
        minutes: int = 60
    ) -> List[OptionMinuteData]:
        """获取最近N分钟的期权数据"""
        from_time = datetime.now() - timedelta(minutes=minutes)
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.option_code == option_code,
                    self.model.trade_time >= from_time
                )
            )
            .order_by(desc(self.model.trade_time))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_realtime_data(self, session: AsyncSession, option_code: str) -> Optional[OptionMinuteData]:
        """获取期权实时数据"""
        stmt = (
            select(self.model)
            .where(self.model.option_code == option_code)
            .order_by(desc(self.model.trade_time))
            .limit(1)
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def calculate_intraday_stats(
        self,
        session: AsyncSession,
        option_code: str,
        trade_date: date
    ) -> Dict[str, Any]:
        """计算日内统计数据"""
        minute_data = await self.get_intraday_data(session, option_code, trade_date)
        
        if not minute_data:
            return {
                'total_volume': 0,
                'total_amount': 0.0,
                'price_range': 0.0,
                'avg_price': 0.0,
                'volatility': 0.0
            }
        
        # 计算总成交量和成交额
        total_volume = sum(data.volume for data in minute_data if data.volume)
        total_amount = sum(float(data.amount) for data in minute_data if data.amount)
        
        # 计算价格范围和平均价格
        prices = [float(data.close_price) for data in minute_data if data.close_price]
        if prices:
            price_range = max(prices) - min(prices)
            avg_price = sum(prices) / len(prices)
            
            # 计算波动率
            if len(prices) > 1:
                returns = [(prices[i] - prices[i-1]) / prices[i-1] for i in range(1, len(prices))]
                import statistics
                volatility = statistics.stdev(returns) if len(returns) > 1 else 0
            else:
                volatility = 0
        else:
            price_range = 0.0
            avg_price = 0.0
            volatility = 0.0
        
        return {
            'total_volume': total_volume,
            'total_amount': total_amount,
            'price_range': price_range,
            'avg_price': avg_price,
            'volatility': volatility
        }


class OptionChainDAO(FinancialDataDAO[OptionChain]):
    """期权链数据访问层"""
    
    model = OptionChain
    
    async def get_option_chain(
        self,
        session: AsyncSession,
        underlying_code: str,
        trade_date: date,
        expire_month: Optional[str] = None
    ) -> List[OptionChain]:
        """获取期权链数据"""
        conditions = [
            self.model.underlying_code == underlying_code,
            self.model.trade_date == trade_date
        ]
        
        if expire_month:
            conditions.append(self.model.expire_month == expire_month)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(asc(self.model.strike_price))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_strike_price(
        self,
        session: AsyncSession,
        underlying_code: str,
        trade_date: date,
        strike_price: Decimal
    ) -> Optional[OptionChain]:
        """根据行权价获取期权链数据"""
        stmt = select(self.model).where(
            and_(
                self.model.underlying_code == underlying_code,
                self.model.trade_date == trade_date,
                self.model.strike_price == strike_price
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_expire_months(
        self,
        session: AsyncSession,
        underlying_code: str,
        trade_date: date
    ) -> List[str]:
        """获取可用的到期月份列表"""
        stmt = (
            select(self.model.expire_month)
            .where(
                and_(
                    self.model.underlying_code == underlying_code,
                    self.model.trade_date == trade_date
                )
            )
            .distinct()
            .order_by(asc(self.model.expire_month))
        )
        result = await session.execute(stmt)
        return [row[0] for row in result.fetchall()]
    
    async def calculate_pcr_ratio(
        self,
        session: AsyncSession,
        underlying_code: str,
        trade_date: date
    ) -> Dict[str, float]:
        """计算Put/Call比率"""
        chain_data = await self.get_option_chain(session, underlying_code, trade_date)
        
        if not chain_data:
            return {
                'pcr_volume': 0.0,
                'pcr_open_interest': 0.0,
                'total_call_volume': 0,
                'total_put_volume': 0,
                'total_call_oi': 0,
                'total_put_oi': 0
            }
        
        call_volume = sum(data.call_volume or 0 for data in chain_data)
        put_volume = sum(data.put_volume or 0 for data in chain_data)
        call_oi = sum(data.call_open_interest or 0 for data in chain_data)
        put_oi = sum(data.put_open_interest or 0 for data in chain_data)
        
        pcr_volume = put_volume / call_volume if call_volume > 0 else 0
        pcr_oi = put_oi / call_oi if call_oi > 0 else 0
        
        return {
            'pcr_volume': pcr_volume,
            'pcr_open_interest': pcr_oi,
            'total_call_volume': call_volume,
            'total_put_volume': put_volume,
            'total_call_oi': call_oi,
            'total_put_oi': put_oi
        }
    
    async def get_atm_options(
        self,
        session: AsyncSession,
        underlying_code: str,
        trade_date: date,
        underlying_price: Decimal,
        tolerance: Decimal = Decimal('0.05')  # 5%容差
    ) -> List[OptionChain]:
        """获取平值期权"""
        min_strike = underlying_price * (1 - tolerance)
        max_strike = underlying_price * (1 + tolerance)
        
        stmt = select(self.model).where(
            and_(
                self.model.underlying_code == underlying_code,
                self.model.trade_date == trade_date,
                self.model.strike_price >= min_strike,
                self.model.strike_price <= max_strike
            )
        ).order_by(asc(func.abs(self.model.strike_price - underlying_price)))
        
        result = await session.execute(stmt)
        return list(result.scalars().all())


class OptionExerciseDataDAO(FinancialDataDAO[OptionExerciseData]):
    """期权行权数据访问层"""
    
    model = OptionExerciseData
    
    async def get_by_option_and_date(
        self,
        session: AsyncSession,
        option_code: str,
        exercise_date: date
    ) -> Optional[OptionExerciseData]:
        """获取指定期权指定日期的行权数据"""
        stmt = select(self.model).where(
            and_(
                self.model.option_code == option_code,
                self.model.exercise_date == exercise_date
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_exercise_history(
        self,
        session: AsyncSession,
        option_code: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[OptionExerciseData]:
        """获取期权行权历史数据"""
        conditions = [self.model.option_code == option_code]
        
        if start_date:
            conditions.append(self.model.exercise_date >= start_date)
        if end_date:
            conditions.append(self.model.exercise_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.exercise_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_daily_exercise_summary(
        self,
        session: AsyncSession,
        exercise_date: date
    ) -> Dict[str, Any]:
        """获取每日行权汇总数据"""
        stmt = select(self.model).where(self.model.exercise_date == exercise_date)
        result = await session.execute(stmt)
        exercise_data = list(result.scalars().all())
        
        if not exercise_data:
            return {
                'total_exercise_volume': 0,
                'total_delivery_volume': 0,
                'total_cash_settlement': 0.0,
                'exercise_count': 0,
                'avg_exercise_ratio': 0.0
            }
        
        total_exercise_volume = sum(data.exercise_volume or 0 for data in exercise_data)
        total_delivery_volume = sum(data.delivery_volume or 0 for data in exercise_data)
        total_cash_settlement = sum(float(data.cash_settlement or 0) for data in exercise_data)
        exercise_count = len(exercise_data)
        
        ratios = [float(data.exercise_ratio) for data in exercise_data if data.exercise_ratio]
        avg_exercise_ratio = sum(ratios) / len(ratios) if ratios else 0
        
        return {
            'total_exercise_volume': total_exercise_volume,
            'total_delivery_volume': total_delivery_volume,
            'total_cash_settlement': total_cash_settlement,
            'exercise_count': exercise_count,
            'avg_exercise_ratio': avg_exercise_ratio
        }
    
    async def get_high_exercise_ratio_options(
        self,
        session: AsyncSession,
        exercise_date: date,
        min_ratio: Decimal = Decimal('0.5'),  # 50%
        limit: int = 20
    ) -> List[OptionExerciseData]:
        """获取高行权比例期权"""
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.exercise_date == exercise_date,
                    self.model.exercise_ratio >= min_ratio
                )
            )
            .order_by(desc(self.model.exercise_ratio))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def calculate_exercise_statistics(
        self,
        session: AsyncSession,
        start_date: date,
        end_date: date
    ) -> Dict[str, Any]:
        """计算行权统计数据"""
        stmt = select(self.model).where(
            and_(
                self.model.exercise_date >= start_date,
                self.model.exercise_date <= end_date
            )
        )
        result = await session.execute(stmt)
        exercise_data = list(result.scalars().all())
        
        if not exercise_data:
            return {
                'total_options_exercised': 0,
                'total_exercise_volume': 0,
                'avg_exercise_ratio': 0.0,
                'max_exercise_ratio': 0.0,
                'total_cash_settlement': 0.0
            }
        
        total_options_exercised = len(exercise_data)
        total_exercise_volume = sum(data.exercise_volume or 0 for data in exercise_data)
        
        ratios = [float(data.exercise_ratio) for data in exercise_data if data.exercise_ratio]
        avg_exercise_ratio = sum(ratios) / len(ratios) if ratios else 0
        max_exercise_ratio = max(ratios) if ratios else 0
        
        total_cash_settlement = sum(float(data.cash_settlement or 0) for data in exercise_data)
        
        return {
            'total_options_exercised': total_options_exercised,
            'total_exercise_volume': total_exercise_volume,
            'avg_exercise_ratio': avg_exercise_ratio,
            'max_exercise_ratio': max_exercise_ratio,
            'total_cash_settlement': total_cash_settlement
        }